package plUpdate;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * User: Jason
 * Date: 8/11/13
 * Time: 6:24 PM
 * To change this template use File | Settings | File Templates.
 */
public class ExportPLwithErrors {



    public ExportPLwithErrors(){

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dataSourceName = "dbLI";
            String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
            Connection conli = DriverManager.getConnection(dbURL, "", "");
            Statement stliPL = conli.createStatement();
            Statement stliUpd = conli.createStatement();

            //Check if output tble exists
            try{
                Connection conlitry = DriverManager.getConnection(dbURL, "", "");
                Statement stliPLtry = conli.createStatement();
                DatabaseMetaData md = conlitry.getMetaData();
                stliUpd.execute("DELETE * FROM plerror");

                ResultSet rs = md.getColumns(null, null, "plerror", "partslink");
                if (rs.next()) {
                    //Column in table exist
                    //                    System.out.println("column exist");
                }else {
                    //                    System.out.println("column not exist");

                    //Fields (PART#, PARTSLINK,PARTSLINKNEW,BAD)
                    stliPLtry.execute("alter table PLError add [PART#] CHAR(10)");
                    stliPLtry.execute("alter table PLError add PARTSLINK CHAR(10)");
                    stliPLtry.execute("alter table PLError add PARTSLINKNEW CHAR(10)");
//                    stliPLtry.execute("alter table PLError add CAPA YESNO");

                }
                stliPLtry.close();
                conlitry.close();



            }

            catch (SQLException er1){
                System.out.println(er1);
            }

            try{
                String[] tbl = new String[]{"tblinventory","tblbcn","tblltn","tblhfn","tblmrn","tblcln","tblotn"};

                String pl="";
                String op1="";
                String oPL="";
                String orgPL="";
                String sql="";
                String updpl=pl;
                String plField="";
                boolean bad=false;
                String originalPLNum="";

                int z=0;

                for (z=0; z<7 ;z++) {
                    if (z==0){
                        plField="Partslink";
                    }else if (z>0) {
                        plField="PARTLINK";
                    }
                    stliPL.execute("select * from "+tbl[z]+"");
                    ResultSet resultSet1 = stliPL.getResultSet();

                    System.out.print("<Start table :"+tbl[z]+" "+plField+">\t\t");
                    if (resultSet1 != null)  {

                        while (resultSet1.next()) {
                            oPL=resultSet1.getString("PART#");
                            pl= resultSet1.getString(plField);
                            originalPLNum= pl;


//                            orgPL=pl;


                            if(pl==null){
                                pl="NO PL";
                            }else if (pl.length() > 9) {
                                pl=pl.replaceAll(" ","");
                                pl=pl.replaceAll("-","");
                                pl=pl.replaceAll("/","");
                            }
                            try{

                                stliUpd.execute(sql);
                                if(originalPLNum.equals(pl)) {
                                    bad=false;
                                }else{
                                    bad=true;
                                }
                                sql="INSERT INTO PLERROR([part#],partslink,partslinknew,bad) VALUES('"+oPL+"','"+originalPLNum+"','"+pl+"',"+bad+")";
//                                    System.out.println("-"+sql);
                                stliUpd.execute(sql);
                                //                            System.out.println(".");
                            }catch (SQLException er){
                                System.out.println(er);
                            }
                            //if PL=0 (null)
//                            try{
////                                stliUpd.execute("DELETE * FROM plerror");
//                                stliUpd.execute("INSERT INTO PLERROR([part#],partslink,partslinknew) VALUES("+oPL+","+null+","+pl+")");
////                                        System.out.println("L:"+updpl);
//
//                                //                            System.out.println(".");
//                            }catch (SQLException er){
//                                System.out.println(er);
//                            }

                            //check if partlink and partslinknew are the same, if not, then BAD=true
                        }
                    }

                }
                stliPL.close();
                stliUpd.close();
                conli.close();

            }catch (SQLException a){}
        }catch (ClassNotFoundException z){}
        catch (SQLException b){}

        System.out.println("*TBLINV PL CLEANUP COMPLETE*");

    }
}